_posts/2017-5-10-Connect Azure App Service to Azure database for MySQL and PostgreSQL via SSL.html

--- title: "Connect Azure App Service to Azure database for MySQL and PostgreSQL via SSL" hide_excerpt: true --- <html><head> <meta charset="utf-8"/> </head> <body> <div id="page"> <a class="url fn n profile-usercard-hover" href="https://social.msdn.microsoft.com/profile/mksunitha" target="_blank">mksunitha</a> <time> 5/10/2017 8:10:26 AM</time> <hr/> <div id="content"><a href="https://azure.microsoft.com/en-us/services/mysql">Azure Database for MySQL (Preview)</a> and <a href="http://azure.microsoft.com/services/postgresql">Azure Database for PostgreSQL (Preview)</a>, both services support Secure Sockets Layer (SSL) encryption. By default if you create a MySQL or PostgreSQL server using this server SSL is required to connect to all databases on that server. With <a href="https://azure.microsoft.com/en-us/services/app-service/">Web Apps</a> , the application needs to provide the certificate authority (CA) is one that the client trusts for the connection to succeed. This involves a few steps . Follow the steps below to add SSL to an existing application on Azure App Service. <div>Note: These instructions are same whether you are using Windows or Linux based Azure App Service.</div> <ol> <li>Follow the steps in this <a href="http://docs.microsoft.com/azure/mysql/howto-configure-ssl.md">article</a> on configuring SSL on Azure database for MySQL or PostgreSQL.</li> <li>Create a <strong>bin</strong> folder under <strong>D:/home/site/wwwroot </strong>and place the certificate <em>(.pem file </em>generated from step #1<em>)</em> in<strong> bin </strong>folder. You can do this directly by accessing the file server for web app using <a href="https://docs.microsoft.com/en-us/azure/app-service-web/web-sites-deploy">FTP or Git or Kudu</a> .</li> <li>Log in to <a href="https://portal.azure.com">Azure portal.</a></li> <li>Select your existing Web app and click on Application settings. Add the following app setting for your web app: For MYSQL : <img alt="ssl-wordpress" class="alignnone size-large wp-image-4285" height="135" src="{{ site.baseurl }}/media/2017/05/ssl-wordpress-1024x157.png" width="879"/> For PostgreSQL :<a href="{{ site.baseurl }}/media/2017/05/ssl-postgres.png"><img alt="ssl-postgres" class="alignnone size-large wp-image-4295" height="118" src="{{ site.baseurl }}/media/2017/05/ssl-postgres-1024x138.png" width="879"/></a></li> <li>Now SSL certificate is added to your web application . Your application code needs to be updated to use this certificate authority when connecting to the database.</li> </ol> Please refer to the documentation of your application framework on how to consume the certificate authority to connect to the database via SSL. Here are a few examples below. <h3>Connect to MySQL server with SSL for WordPress app</h3> Add the following to <em><strong>wp-config.php</strong></em> to connect to the MySQL database via SSL <pre>define('MYSQL_CLIENT_FLAGS', MYSQL_CLIENT_SSL); define( 'MYSQL_SSL_CA', getenv('<span>MYSQL_SSL_CA')</span>); </pre> <h3>Note:</h3> If you are using PHP 7.X version , you need another flag <span> <code>MYSQLI_CLIENT_SSL_DONT_VERIFY_SERVER_CERT</code> for any PHP based application. For example with WordPress , you need to update MYSQL_CLIENT_FLAGS as shown below </span> <pre><code>define( 'MYSQL_CLIENT_FLAGS', MYSQLI_CLIENT_SSL | MYSQLI_CLIENT_SSL_DONT_VERIFY_SERVER_CERT ); </code></pre> <h3>Connect to PostgreSQL server with SSL for Drupal app</h3> Add the following to <strong><em>site/default/settings.php</em></strong> and add PDO options for SSL <pre class="default prettyprint prettyprinted">$databases = array ( 'default' => array ( 'default' = array ( 'database' => 'databasename', 'username' => 'username', 'password' => 'password', 'host' => 'hostname', 'port' => '3306', 'driver' => 'mysql', 'prefix' => '', 'pdo' => array( PDO::PGSQL_ATTR_SSL_CA => getenv('POSTGRESQL<span>_SSL_CA'</span>), ), ), );</pre> <h3>Connect to MySQL server with SSL for Drupal app</h3> Add the following to <strong><em>site/default/settings.php</em></strong> and add PDO options for SSL <pre class="default prettyprint prettyprinted">$databases = array ( 'default' => array ( 'default' => array ( 'database' => 'databasename', 'username' => 'username', 'password' => 'password', 'host' => 'hostname', 'port' => '3306', 'driver' => 'mysql', 'prefix' => '', 'pdo' => array( PDO::MYSQL_ATTR_SSL_CA => getenv('MYSQL_SSL_CA'), ), ), );</pre> <h3>Connect to PostgreSQL server with SSL for Django app</h3> <span>Add the following to <strong><em>settings.py</em></strong> and the options for SSL </span> <pre>DATABASES = { 'default': { 'ENGINE': 'django.db.backends.postgresql_psycopg2', 'NAME': 'dbname', 'USER': 'dbuser', 'PASSWORD': 'dbpassword', 'HOST': 'dbhost', 'OPTIONS': { 'sslmode': 'require', 'ca':os.environ.get('POSTGRESQL_SSL_CA', '') }, }, } </pre></div> </div></body> <script src="{{ site.baseurl }}/resource/jquery-1.12.1.min.js" type="text/javascript"></script> <script src="{{ site.baseurl }}/resource/replace.js" type="text/javascript"></script> </html>

_posts/2017-5-10-Connect Azure App Service to Azure database for MySQL and PostgreSQL via SSL.html (92 lines of code) (raw):